
/**
	Author	    : Rohan
	Date	    : July 2nd 2004
	Description : This proc will insert or update the line item details. It is called from
		      trigger on the Orders table in Epicore for updating the CT database with the
		      changes in the epicore database
*/
ALTER  proc sp_save_epicore_line_item 
(
@line_item_number        int, 
@sales_order_number      varchar(50),
@part_number             varchar(50), 
@cust_name               varchar(50),
@qty                     int, 
@required_date           datetime, 
@unit_price              float,
@user_id                 varchar(10) , 
@user_name               varchar(50), 
@reason                  varchar (50), 
@tax_type                varchar(40),
@li_status               char(1), 
@line_item_desc          varchar(500),
@po_line_item_no         int
)

as
begin

declare @sales_order_id  int
declare @line_item_id    int
declare @amount          float
declare @is_taxable      int
declare @is_cancel       int 
declare @is_expedite     int
declare @product_id      int
declare @line_item_loop  int
declare @line_item_update_status     int
declare @is_only_new_lineitem_change int

--Initialisations
set @line_item_id   = -1
set @sales_order_id = -1
set @is_expedite    = 1
set @line_item_loop = 0 --Always indicate that there are more line item to be saved, so that no record is entered in Change sales order 
set @is_only_new_lineitem_change = 2 --Always indicate not to insert the record in change sales order
set @line_item_update_status     = 0 --Indicates that no record is needed to be inserted in the change sales order table
set @amount = @qty * @unit_price

if( ( @tax_type is null ) or ( rtrim(@tax_type) = '' ) )
begin
	--line item is not taxable
	set @is_taxable = 1
end
else
begin
	--line item is taxable
	set @is_taxable = 0
end

--If status is Void or Cancelled then the line item is cancelled
if( @li_status = 'V' OR @li_status = 'X' )
begin
	--Line item is cancelled
	set @is_cancel = 0
end
else
begin
	--Line item is not cancelled
	set @is_cancel = 1
end


--selecting the product ID of the given part number
select @product_id = product_id from product where Part_number = @part_number and P_Marketing_Part_Number is not null

--Retreving Sales Order details
select @sales_order_id = SalesOrder_Id from Sales_Order where SalesOrder_No = @sales_order_number

if( @@rowcount <> 0 )
begin
	--Checking if the line item is existing
	select @line_item_id = LineItem_ID from Customer_Open_Order_LineItem where SalesOrder_Id = @sales_order_id and COOL_Line_Item_No = @line_item_number
	if( @@ROWCOUNT = 0 )
	begin
		--If the line item is existing in the COOL table then searchig the CCOL table
		select @line_item_id = LineItem_ID from Customer_Cancel_Order_LineItem where SalesOrder_Id = @sales_order_id and CCOL_Line_Item_No = @line_item_number
	end
	
	if(  @line_item_id = - 1 )
	begin
		--inserting line item details
		exec sp_insert_lineItem_proc @sales_order_id, @line_item_number, @product_id, @line_item_desc, @qty, @unit_price, @amount, @required_date, @user_id, @is_taxable, @po_line_item_no, @is_only_new_lineitem_change, @line_item_loop, @is_expedite
	end
	else
	begin
		if( @is_cancel = 0 )
		begin
			--cancel the line item															
			exec sp_cancel_lineItem_proc @line_item_id, @user_id, @is_cancel
		end
		else
		begin
			--updating line item details
			exec sp_change_lineItem_proc @line_item_id, @sales_order_id, @qty, @required_date, @unit_price, @amount, @user_id, @user_name, @reason, @is_taxable, @is_cancel, @line_item_desc, 1, @line_item_loop, @po_line_item_no, @line_item_update_status OUT, @line_item_update_status, @is_expedite 
		end
	end

	--If there is User Hold on the sales order in Epicore then set the financial hold else remove all the holds of all the line item of this sales order
	if( @li_status = 'A' )
	begin
		exec sp_ct_remove_sales_order_holds @sales_order_id, 1, 1, 1, 0
	end
	else
	begin
		exec sp_ct_remove_sales_order_holds @sales_order_id, 1, 1, 1, 1
		
		--If the line item is cancelled then confirm the cancellation since 
		if( @is_cancel = 0 )
		begin
			exec cancel_order_proc @line_item_id, @sales_order_id, @user_id, @user_name
		end
	end
end
end --end of proc


